/**************************************************/

const startTime = "2024-12-01 00:00:00";
const endTime = "2025-02-28 23:59:59";

/**************************************************/

const fs = require("fs");
const path = require("path");
const mysql = require("mysql");
const dayjs = require("dayjs");

const handleQuery = async (query) => {
  const host = "localhost";
  const user = "cdapp";
  const password = "gzzxjjwt1";
  const database = "cdapp";
  // 创建数据库连接
  const connection = mysql.createConnection({ host, user, password, database });
  return new Promise((resolve, reject) => {
    // 连接到数据库
    connection.connect();

    // 执行查询
    connection.query(query, (error, results, fields) => {
      if (error) {
        // 如果查询出错，关闭连接并拒绝 Promise
        connection.end();
        return reject(error);
      }

      // 遍历查询结果
      const parsedResults = results.map((row) => {
        // 遍历每一行的字段
        for (const key in row) {
          if (typeof row[key] === "string") {
            try {
              // 尝试将字符串解析为 JSON
              const parsedValue = JSON.parse(row[key]);
              // 如果解析成功，则将字段值替换为解析后的对象
              row[key] = parsedValue;
            } catch (e) {
              // 如果解析失败，说明不是 JSON 字符串，忽略
            }
          }
        }
        return row; // 返回处理后的行
      });

      // 关闭数据库连接
      connection.end();

      // 返回处理后的结果
      resolve(parsedResults);
    });
  });
};

const handleSave = (input, filename) => {
  let filePath;
  let data;

  if (typeof input === "string") {
    // 如果输入是字符串，保存为txt文件
    filePath = path.join(__dirname, `${filename}.txt`);
    data = input;
  } else {
    // 如果输入是对象或数组，保存为json文件
    filePath = path.join(__dirname, `${filename}.json`);
    data = JSON.stringify(input, null, 2); // 格式化JSON
  }

  // 写入文件
  fs.writeFile(filePath, data, (err) => {
    if (err) {
      console.error("保存文件时出错:", err);
    } else {
      console.log(`文件已保存为 ${filePath}`);
    }
  });
};

const queryFlowData = async () => {
  try {
    const sql = `SELECT * FROM \`2kt_flow_record\` WHERE \`moment\` >= '${startTime}' AND \`moment\` <= '${endTime}'`;
    const results = await handleQuery(sql);
    return results;
    // console.log(JSON.stringify(results, null, 2)); // 输出处理后的结果
  } catch (error) {
    console.error("查询出错:", error);
  }
};

const summaryFlowData = (data, saveJson = false) => {
  const result = {
    monMax: {
      month: "",
      value: "",
    },
    monDetails: {},
  };

  // monDetails:{M_01:{month:'',value:0,dayMax:{date:'',value:''},dayAvg:0,dayDetails:{}}, ...};
  // dayDetails:{D_01:{date:'',value:0}, ...};

  data.forEach((line) => {
    let day = dayjs(line.moment);
    let value = 0;
    line.content.forEach((val) => {
      value += val;
    });
    let keyOfMon = "M_" + day.format("MM");
    let keyOfDay = "D_" + day.format("DD");
    if (result.monDetails[keyOfMon]) {
      // 当前月对象已存在
      result.monDetails[keyOfMon].value += value;
      if (result.monMax.value < result.monDetails[keyOfMon].value) {
        // 当前月对象最大值
        result.monMax.month = result.monDetails[keyOfMon].month;
        result.monMax.value = result.monDetails[keyOfMon].value;
      }
    } else {
      // 当前月对象不存在
      result.monDetails[keyOfMon] = {
        month: day.format("YYYY-MM"),
        value: value,
        dayMax: { date: "", value: 0 },
        dayAvg: 0,
        dayDetails: {},
      };
    }
    //
    if (result.monDetails[keyOfMon].dayDetails[keyOfDay]) {
      // 当前日对象已存在
      result.monDetails[keyOfMon].dayDetails[keyOfDay].value += value;
      if (
        result.monDetails[keyOfMon].dayMax.value <
        result.monDetails[keyOfMon].dayDetails[keyOfDay].value
      ) {
        // 当前日对象最大值
        result.monDetails[keyOfMon].dayMax.date =
          result.monDetails[keyOfMon].dayDetails[keyOfDay].date;
        result.monDetails[keyOfMon].dayMax.value =
          result.monDetails[keyOfMon].dayDetails[keyOfDay].value;
      }
      let dayCount = Object.keys(result.monDetails[keyOfMon].dayDetails).length;
      if (dayCount) {
        result.monDetails[keyOfMon].dayAvg = Math.round(
          result.monDetails[keyOfMon].value / dayCount
        );
      }
    } else {
      // 当前日对象不存在
      result.monDetails[keyOfMon].dayDetails[keyOfDay] = {
        date: day.format("YYYY-MM-DD"),
        value: value,
      };
    }
  });

  // 输出统计结果
  // console.log("流量类相关指标", result);
  if (saveJson) handleSave(result, "流量类相关指标");
  return result;
};

const butifyFlowData = (data, saveFile = false) => {
  let markdown = `# Monthly Data Report\n\n`;

  // Add monMax section
  markdown += `## Maximum Monthly Value\n`;
  markdown += `- **Month**: ${data.monMax.month}\n`;
  markdown += `- **Value**: ${data.monMax.value}\n\n`;

  // Add monDetails section
  markdown += `## Monthly Details\n`;
  for (const [monthKey, monthData] of Object.entries(data.monDetails)) {
    markdown += `### ${monthData.month}\n`;
    markdown += `- **Total Value**: ${monthData.value}\n`;
    markdown += `- **Maximum Daily Value**: ${monthData.dayMax.value} (on ${monthData.dayMax.date})\n`;
    markdown += `- **Average Daily Value**: ${monthData.dayAvg}\n\n`;

    // Add dayDetails section
    markdown += `#### Daily Details\n`;
    for (const [dayKey, dayData] of Object.entries(monthData.dayDetails)) {
      markdown += `- **Date**: ${dayData.date}, **Value**: ${dayData.value}\n`;
    }
    markdown += `\n`;
  }
  if (saveFile) fs.writeFileSync("FlowReport.md", markdown);
  return markdown;
};

const querySectorData = async () => {
  try {
    const sql = `SELECT * FROM \`2kt_sector_record\` WHERE \`endTime\` >= '${startTime}' AND \`startTime\` <= '${endTime}'`;
    const results = await handleQuery(sql);
    return results;
  } catch (error) {
    console.error("查询出错:", error);
  }
};

const summarySectorData = (data, saveJson = false) => {
  const result = {};
  // console.log("summarySectorData", data);

  // 统计 AP7L 和 AP7R 不在同一个席位 或者  APL7 和 APR7 不在同一个席位的时段
  data.forEach((line) => {
    const day = dayjs(line.startTime);
    const keyOfMon = "M_" + day.format("MM");
    const keyOfDay = "D_" + day.format("MMDD");
    let value = 0;
    Object.keys(line.content).forEach((seatCode) => {
      const seat = line.content[seatCode];
      if (!value && (seat.includes("AP7L") || seat.includes("AP7R"))) {
        // 未判定为独立进近 且 包含向北一边或五边扇区
        if (seat.includes("AP7L") && seat.includes("AP7R")) {
          // 同时包含一边和五边扇区 非向北独立进近
        } else {
          // 向北独立进近
          value = 1;
        }
      }
      if (!value && (seat.includes("APL7") || seat.includes("APR7"))) {
        // 未判定为独立进近 且 包含向南一边或五边扇区
        if (seat.includes("APL7") && seat.includes("APR7")) {
          // 同时包含一边和五边扇区 非向南独立进近
        } else {
          // 向南独立进近
          value = 1;
        }
      }
    });
    if (value) {
      // 判定为独立进近
      if (!result[keyOfMon]) {
        result[keyOfMon] = {
          month: day.format("YYYY-MM"),
          value: 0,
          average: 0,
          details: {},
        };
      }
      if (!result[keyOfMon].details[keyOfDay])
        result[keyOfMon].details[keyOfDay] = {
          date: dayjs(line.startTime).format("YYYY-MM-DD"),
          value: 0,
        };
      const duration = dayjs(line.endTime).diff(line.startTime, "hour", true);
      result[keyOfMon].value += duration;
      result[keyOfMon].details[keyOfDay].value += duration;
      result[keyOfMon].average =
        result[keyOfMon].value / Object.keys(result[keyOfMon].details).length;
    }
  });
  if (saveJson) handleSave(result, "独立进近指标");
  return result;
};

const butifySectorData = (json, saveFile = false) => {
  let markdown = "# Independent Approach Report\n\n";

  for (const [key, monthData] of Object.entries(json)) {
    markdown += `## ${monthData.month}\n`;
    markdown += `**Total:** ${
      Math.round(monthData.value * 100) / 100
    } h\t**Average:** ${Math.round(monthData.average * 100) / 100} h / day\n\n`;

    if (Object.keys(monthData.details).length > 0) {
      markdown += "### Daily Details:\n";
      for (const [detailKey, detail] of Object.entries(monthData.details)) {
        markdown += `- **Date:** ${detail.date}\t**Value:** ${
          Math.round(detail.value * 100) / 100
        } h\n`;
      }
    }

    markdown += "\n";
  }

  if (saveFile) fs.writeFileSync("SectorReport.md", markdown);
  return markdown;
};

(async () => {
  const flowData = await queryFlowData();
  const flowResult = summaryFlowData(flowData);
  const flowReport = butifyFlowData(flowResult);
  const sectorData = await querySectorData();
  const sectorResult = summarySectorData(sectorData);
  const sectorReport = butifySectorData(sectorResult);
  const md = `${flowReport}\n\n${sectorReport}`;
  fs.writeFileSync("Report.md", md);
})();
